/*
 Navicat MySQL Data Transfer

 Source Server         : MySQL8
 Source Server Type    : MySQL
 Source Server Version : 80020
 Source Host           : localhost
 Source Database       : mall

 Target Server Type    : MySQL
 Target Server Version : 80020
 File Encoding         : utf-8

 Date: 07/14/2020 11:41:57 AM
*/

SET NAMES utf8;
SET FOREIGN_KEY_CHECKS = 0;

-- ----------------------------
--  Table structure for `tb_buyer`
-- ----------------------------
DROP TABLE IF EXISTS `tb_buyer`;
CREATE TABLE `tb_buyer` (
  `bid` int NOT NULL AUTO_INCREMENT COMMENT '买家id',
  `bname` varchar(50) DEFAULT NULL COMMENT '买家名',
  `sex` varchar(50) DEFAULT NULL COMMENT '买家性别',
  `age` int DEFAULT NULL COMMENT '买家年龄',
  `tel` varchar(50) DEFAULT NULL COMMENT '买家电话',
  `email` varchar(50) DEFAULT NULL COMMENT '买家邮箱',
  `address` varchar(50) DEFAULT NULL COMMENT '买家地址',
  `stat` int DEFAULT NULL COMMENT '买家状态',
  `deleted` int DEFAULT '0',
  `create_time` datetime DEFAULT NULL,
  `modify_time` datetime DEFAULT NULL,
  `password` varchar(50) DEFAULT NULL COMMENT '买家密码',
  PRIMARY KEY (`bid`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 COMMENT='买家表';

-- ----------------------------
--  Records of `tb_buyer`
-- ----------------------------
BEGIN;
INSERT INTO `tb_buyer` VALUES (1, '李饼', '男', 27, '16315758915', '77193@163.com', '河南洛阳', NULL, 0, '2020-07-09 23:46:49', '2020-07-09 23:46:49', '1aa6e65b8b057650b6231a6f3c7bb634');
INSERT INTO `tb_buyer` VALUES (2, '李包', '男', 18, '15594170745', '26717@163.com', '河南洛阳', NULL, 0, '2020-07-09 23:46:49', '2020-07-09 23:46:49', 'e45bd9f9f421ba7acc5880ae8e2d7907');
INSERT INTO `tb_buyer` VALUES (3, '陈拾', '男', 20, '10026447428', '13480@163.com', '河南洛阳', NULL, 0, '2020-07-09 23:46:49', '2020-07-09 23:46:49', '6234320d29c29dde0169360e767785f8');
INSERT INTO `tb_buyer` VALUES (4, '蔡疏', '男', 40, '18686419224', '80973@163.com', '陕西西安', NULL, 0, '2020-07-09 23:46:49', '2020-07-09 23:46:49', '1fe51bb742c1e27d255e5e7e1f1ab688');
INSERT INTO `tb_buyer` VALUES (5, '王七', '男', 20, '19365706922', '73201@163.com', '北京', NULL, 0, '2020-07-09 23:46:49', '2020-07-09 23:46:49', '236dd88b3962d2a4a7b1e829b68e727b');
INSERT INTO `tb_buyer` VALUES (6, '崔倍', '男', 20, '17742871779', '89562@163.com', '湖南长沙', NULL, 0, '2020-07-09 23:46:49', '2020-07-09 23:46:49', '8ad83e04f1d32b8070bd7d54a5fd693e');
INSERT INTO `tb_buyer` VALUES (7, '孙豹', '男', 20, '16973203159', '64997@163.com', '江西南昌', NULL, 0, '2020-07-09 23:46:49', '2020-07-09 23:46:49', '9bac1a6429924cb5e2462c653414b809');
INSERT INTO `tb_buyer` VALUES (8, '来俊臣', '男', 25, '14894484102', '86859@163.com', '湖北武汉', NULL, 0, '2020-07-09 23:46:49', '2020-07-09 23:46:49', 'd9ba40c6de0666fd10f95099ce80d705');
INSERT INTO `tb_buyer` VALUES (9, '朗百灵', '女', 19, '16448906871', '77433@163.com', '内蒙古呼和浩特', NULL, 0, '2020-07-09 23:46:49', '2020-07-09 23:46:49', 'f8e5cf3b9c6ff3ffd22e0e751368a98c');
INSERT INTO `tb_buyer` VALUES (10, '血蝉', '女', 23, '12608733275', '65193@163.com', '陕西西安', NULL, 0, '2020-07-09 23:46:49', '2020-07-09 23:46:49', '41910c949b47ba110f71d2110dc85d79');
INSERT INTO `tb_buyer` VALUES (11, '武明空', '女', 68, '13353622677', '83746@163.com', '四川成都', NULL, 0, '2020-07-09 23:46:49', '2020-07-09 23:46:49', 'e1500736f23b02bb229d5efffd5bb6f7');
COMMIT;

-- ----------------------------
--  Table structure for `tb_cart`
-- ----------------------------
DROP TABLE IF EXISTS `tb_cart`;
CREATE TABLE `tb_cart` (
  `cart_id` int NOT NULL AUTO_INCREMENT COMMENT '购物id',
  `buyer_id` int NOT NULL COMMENT '买家id',
  `good_id` int NOT NULL COMMENT '商品id',
  `deleted` int DEFAULT '0',
  `quantity` int DEFAULT '1' COMMENT '商品数量',
  PRIMARY KEY (`cart_id`),
  KEY `buyer_id` (`buyer_id`),
  KEY `good_id` (`good_id`),
  CONSTRAINT `tb_cart_ibfk_1` FOREIGN KEY (`buyer_id`) REFERENCES `tb_buyer` (`bid`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `tb_cart_ibfk_2` FOREIGN KEY (`good_id`) REFERENCES `tb_goods` (`gid`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 COMMENT='购物车表';

-- ----------------------------
--  Records of `tb_cart`
-- ----------------------------
BEGIN;
INSERT INTO `tb_cart` VALUES ('1', '1', '3', '0', '1'), ('2', '2', '11', '0', '5'), ('3', '5', '1', '0', '4'), ('4', '5', '6', '0', '10'), ('5', '5', '7', '0', '1'), ('6', '8', '1', '0', '6'), ('7', '3', '7', '0', '21'), ('8', '7', '2', '0', '4'), ('9', '1', '2', '0', '31'), ('10', '1', '8', '0', '5'), ('11', '1', '3', '0', '1');
COMMIT;

-- ----------------------------
--  Table structure for `tb_category`
-- ----------------------------
DROP TABLE IF EXISTS `tb_category`;
CREATE TABLE `tb_category` (
  `cid` int NOT NULL AUTO_INCREMENT COMMENT '类别编号',
  `cname` varchar(50) DEFAULT NULL COMMENT '类别名称',
  `create_time` datetime DEFAULT NULL,
  `modify_time` datetime DEFAULT NULL,
  `deleted` int DEFAULT '0',
  PRIMARY KEY (`cid`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

-- ----------------------------
--  Records of `tb_category`
-- ----------------------------
BEGIN;
INSERT INTO `tb_category` VALUES ('1', '床品', '2020-07-09 23:32:14', '2020-07-09 23:32:21', '0'), ('2', '灯具', '2020-07-09 23:33:38', '2020-07-09 23:33:45', '0'), ('3', '布艺', '2020-07-09 23:34:02', '2020-07-09 23:34:19', '0'), ('4', '收纳', '2020-07-09 23:34:11', '2020-07-09 23:34:21', '0'), ('5', '小家具', '2020-07-09 23:34:17', '2020-07-09 23:44:12', '0');
COMMIT;

-- ----------------------------
--  Table structure for `tb_goods`
-- ----------------------------
DROP TABLE IF EXISTS `tb_goods`;
CREATE TABLE `tb_goods` (
  `gid` int NOT NULL AUTO_INCREMENT COMMENT '商品编号',
  `gname` varchar(50) DEFAULT NULL COMMENT '商品名称',
  `price` varchar(50) DEFAULT NULL COMMENT '商品价格',
  `quantity` int DEFAULT NULL COMMENT '商品库存数量',
  `images` varchar(50) DEFAULT NULL COMMENT '图片',
  `gdesc` text COMMENT '商品概述',
  `create_time` datetime DEFAULT NULL COMMENT '创建时间',
  `modify_time` datetime DEFAULT NULL COMMENT '修改时间',
  `deleted` int DEFAULT '0',
  `cateid` int DEFAULT NULL COMMENT '商品类别id',
  PRIMARY KEY (`gid`),
  KEY `cateid` (`cateid`),
  CONSTRAINT `cateid` FOREIGN KEY (`cateid`) REFERENCES `tb_category` (`cid`) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 COMMENT='商品表';

-- ----------------------------
--  Records of `tb_goods`
-- ----------------------------
BEGIN;
INSERT INTO `tb_goods` VALUES (1, '小女孩画夹挂布', '18.9', 80, '777a802f-56a1-4e88-a8eb-8a6fa3541f26.jpg', '北欧风格|材质：布', '2020-07-10 00:18:17', '2020-07-10 00:18:17', 0, 5);
INSERT INTO `tb_goods` VALUES (2, '北欧风布艺沙发', '1799', 30, 'cc06196b-949b-4a55-8eae-42430fe7c8d5.jpg', '简约现代风格|支持拆洗|填充物：海绵', '2020-07-10 00:00:07', '2020-07-10 00:00:34', 0, 3);
INSERT INTO `tb_goods` VALUES (3, '圣尼卡港式轻奢布艺软包沙发', '17432', 10, 'cc06196b-949b-4a55-8eae-42430fe7c8d5.jpg', '轻奢风格|不支持拆洗|填充物：羽绒', '2020-07-10 00:01:34', '2020-07-10 00:01:34', 0, 3);
INSERT INTO `tb_goods` VALUES (4, 'LED吸顶灯', '9.9', 60, 'd3f1b456-82f2-44ad-9502-6b3fe500b046.jpg', '简约现代风格|亚克力吸顶灯', '2020-07-10 00:04:05', '2020-07-10 00:04:05', 0, 2);
INSERT INTO `tb_goods` VALUES (5, '马卡龙圆形灯饰', '23.9', 40, 'd3f1b456-82f2-44ad-9502-6b3fe500b046.jpg', '北欧风格|木艺吸顶灯', '2020-07-10 00:05:08', '2020-07-10 00:05:08', 0, 2);
INSERT INTO `tb_goods` VALUES (6, '水洗棉夏季卡通四件套', '39', 100, '505efab6-bfc1-420e-a73b-218d7e99a113.jpg', '材质：涤纶|卡通风|磨毛', '2020-07-10 00:06:50', '2020-07-10 00:06:50', 0, 1);
INSERT INTO `tb_goods` VALUES (7, '无印风日式水洗棉床单三件套', '39', 120, '777a802f-56a1-4e88-a8eb-8a6fa3541f26.jpg', '材质：涤纶|日式', '2020-07-10 00:08:53', '2020-07-10 00:08:53', 0, 1);
INSERT INTO `tb_goods` VALUES (8, '家用无纺布收纳箱', '4.9', 300, '777a802f-56a1-4e88-a8eb-8a6fa3541f26.jpg', '材质：无纺布', '2020-07-10 00:10:16', '2020-07-10 00:10:16', 0, 4);
INSERT INTO `tb_goods` VALUES (9, '办公桌面收纳箱', '10.9', 200, '505efab6-bfc1-420e-a73b-218d7e99a113.jpg', '材质：塑料|日式风格', '2020-07-10 00:11:26', '2020-07-10 00:11:45', 0, 4);
INSERT INTO `tb_goods` VALUES (10, '康巴斯挂钟', '38.5', 70, '63136786-9939-4473-b8f5-0251fcea534b.jpg', '木|简约现代|三唯一居', '2020-07-10 00:14:26', '2020-07-10 00:14:26', 0, 5);
INSERT INTO `tb_goods` VALUES (11, '创意摆件家居装饰品', '50', 20, '63136786-9939-4473-b8f5-0251fcea534b.jpg', '人物造型|场景：日常送礼', '2020-07-10 00:16:27', '2020-07-10 00:16:27', 0, 5);COMMIT;

-- ----------------------------
--  Table structure for `tb_logistic`
-- ----------------------------
DROP TABLE IF EXISTS `tb_logistic`;
CREATE TABLE `tb_logistic` (
  `lid` int NOT NULL AUTO_INCREMENT COMMENT '物流公司编号',
  `lname` varchar(50) DEFAULT NULL COMMENT '物流公司名称',
  `ltel` varchar(50) DEFAULT NULL COMMENT '物流公司电话',
  `lemail` varchar(50) DEFAULT NULL COMMENT '物流公司邮件',
  `laddress` varchar(50) DEFAULT NULL COMMENT '物流公司地址',
  `deleted` int DEFAULT '0',
  PRIMARY KEY (`lid`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COMMENT='物流公司表';

-- ----------------------------
--  Records of `tb_logistic`
-- ----------------------------
BEGIN;
INSERT INTO `tb_logistic` VALUES ('1', '顺丰', '1234567', '333333@163.com', '西安', '0'), ('2', '中通', '3542788', '111111@163.com', '天津', '0'), ('3', '圆通', '7538597', '222222@163.com', '上海', '0');
COMMIT;

-- ----------------------------
--  Table structure for `tb_order`
-- ----------------------------
DROP TABLE IF EXISTS `tb_order`;
CREATE TABLE `tb_order` (
  `oid` int NOT NULL AUTO_INCREMENT COMMENT '订单编号',
  `good_id` int NOT NULL COMMENT '商品编号',
  `user_id` int NOT NULL COMMENT '商户id',
  `logi_id` int COMMENT '物流公司id',
  `buyer_id` int NOT NULL COMMENT '买家id',
  `create_time` datetime DEFAULT NULL COMMENT '订单创建时间',
  `modify_time` datetime DEFAULT NULL COMMENT '订单修改时间',
  `stat` int DEFAULT NULL COMMENT '订单状态:0->待付款；1->待发货；2->已发货；3->已完成；4->已取消',
  `deleted` int DEFAULT '0',
  `goods_quantity` int NOT NULL DEFAULT '0' COMMENT '商品数量',
  PRIMARY KEY (`oid`),
  KEY `good_id` (`good_id`),
  KEY `user_id` (`user_id`),
  KEY `logi_id` (`logi_id`),
  KEY `buyer_id` (`buyer_id`),
  CONSTRAINT `tb_order_ibfk_2` FOREIGN KEY (`user_id`) REFERENCES `tb_user` (`uid`) ON UPDATE CASCADE,
  CONSTRAINT `tb_order_ibfk_3` FOREIGN KEY (`logi_id`) REFERENCES `tb_logistic` (`lid`) ON UPDATE CASCADE,
  CONSTRAINT `tb_order_ibfk_4` FOREIGN KEY (`buyer_id`) REFERENCES `tb_buyer` (`bid`) ON UPDATE CASCADE,
  CONSTRAINT `tb_order_ibfk_5` FOREIGN KEY (`good_id`) REFERENCES `tb_goods` (`gid`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8 COMMENT='订单表';


# 触发器，用于防止购买量大于库存
drop TRIGGER if EXISTS insert_order;
DELIMITER $$
create trigger insert_order before insert on tb_order
for each row
begin
	declare repoQuantity int; # 库存
	# 获取库存
	select quantity into repoQuantity from tb_goods where gid=new.good_id;
	
	# 如果购买量大于库存，则主动产生错误使插入失败
	if new.goods_quantity > repoQuantity then
		delete from tb_order where oid=new.oid;# 插入的错误语句
	end if;
end$$
DELIMITER ;


-- ----------------------------
--  Records of `tb_order`
-- ----------------------------
BEGIN;
COMMIT;

-- ----------------------------
--  Table structure for `tb_user`
-- ----------------------------
DROP TABLE IF EXISTS `tb_user`;
CREATE TABLE `tb_user` (
  `uid` int NOT NULL AUTO_INCREMENT COMMENT '商户id',
  `uname` varchar(50) DEFAULT NULL COMMENT '商户名',
  `deleted` int DEFAULT '0',
  `create_time` datetime DEFAULT NULL,
  `modify_time` datetime DEFAULT NULL,
  `password` varchar(50) DEFAULT NULL COMMENT '商户密码',
  PRIMARY KEY (`uid`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COMMENT='商家表';

-- ----------------------------
--  Records of `tb_user`
-- ----------------------------
BEGIN;
INSERT INTO `tb_user` VALUES (1, '李四', 0, '2020-07-10 08:52:25', '2020-07-10 08:58:24', '952843e1447e4be3404a135c899cba86');
COMMIT;

SET FOREIGN_KEY_CHECKS = 1;
